/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Dao;

import Entity.Billing;
import Entity.Doctor;
import Entity.Patient;
import Entity.Report;
import Entity.Staff;
import Entity.Stock;
import Entity.Test;
import java.awt.List;
import java.beans.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author NgocHuy
 */
public class PathologyDAO {

    private Connection cn;
    private PreparedStatement pstm;
    private String sql;
    private Statement s;

    public PathologyDAO() {
        cn = DB_Util.getConnection();
    }

    /*---------------------resultSet-------------------------------*/
    private java.util.List<Doctor> getDoctor(ResultSet rs) throws SQLException {
        java.util.List<Doctor> list = new ArrayList<Doctor>();
        while (rs.next()) {

            Doctor d = new Doctor();
            d.setDoctorID(rs.getInt("DoctorID"));
            d.setDName(rs.getString("D_Name"));
            d.setAddress(rs.getString("Address"));
            d.setPhoneNo(rs.getInt("PhoneNo"));
            d.setUsername(rs.getString("Username"));
            d.setPassword(rs.getString("Password"));
            d.setLevel(rs.getInt("Level"));
            d.setSpecialization(rs.getString("Specialization"));
            list.add(d);
        }

        return list;

    }

    private java.util.List<Stock> getStock(ResultSet rs) throws SQLException {
        java.util.List<Stock> list = new ArrayList<Stock>();
        while (rs.next()) {

            Stock s = new Stock();
            s.setItemCode(rs.getInt("ItemCode"));
            s.setIName(rs.getString("IName"));
            s.setICost(rs.getInt("ICost"));
            s.setICost(rs.getInt("ICost"));
            s.setINum(rs.getInt("INum"));
            s.setDNum(rs.getInt("DNum"));
            list.add(s);
        }

        return list;

    }

    private java.util.List<Test> getTest(ResultSet rs) throws SQLException {
        java.util.List<Test> list = new ArrayList<Test>();
        while (rs.next()) {

            Test t = new Test();
            t.setCost(rs.getInt("Cost"));
            t.setTName(rs.getString("TName"));
            t.setTestID(rs.getInt("TestID"));

            list.add(t);
        }

        return list;

    }

    private java.util.List<Staff> getStaff(ResultSet rs) throws SQLException {
        java.util.List<Staff> list = new ArrayList<Staff>();
        while (rs.next()) {

            Staff s = new Staff();
            s.setAddress(rs.getString("Address"));
            s.setDOB(rs.getDate("DOB"));
            s.setDOJ(rs.getDate("DOJ"));
            s.setDept(rs.getString("Dept"));
            s.setEdu_qual(rs.getString("Edu_qual"));
            s.setFName(rs.getString("Fname"));
            s.setGender(rs.getBoolean("Gender"));
            s.setLName(rs.getString("LName"));
            s.setLevel(rs.getInt("Level"));
            s.setPassword(rs.getString("Password"));
            s.setPhoneNo(rs.getInt("P_No"));
            s.setStaffID(rs.getInt("StaffID"));
            s.setTech_skills(rs.getString("Tech_skills"));
            s.setUsername(rs.getString("Username"));
            s.setStatus(rs.getBoolean("Status"));
            list.add(s);
        }

        return list;

    }

    private java.util.List<Staff> getPStaff(ResultSet rs) throws SQLException {
        java.util.List<Staff> list = new ArrayList<Staff>();
        while (rs.next()) {

            Staff s = new Staff();
            s.setStaffID(rs.getInt("StaffID"));
            s.setFName(rs.getString("Name"));
            s.setDOJ(rs.getDate("DOJ"));
            s.setGender(rs.getBoolean("Gender"));
            s.setUsername(rs.getString("Username"));
            s.setStatus(rs.getBoolean("Status"));
            list.add(s);
        }

        return list;

    }

    private java.util.List<Patient> getPatient(ResultSet rs) throws SQLException {
        java.util.List<Patient> list = new ArrayList<Patient>();
        while (rs.next()) {
            Patient p = new Patient();
            p.setPatientID(rs.getInt("PatientID"));
            p.setFName(rs.getString("FName"));
            p.setLName(rs.getString("LName"));
            p.setDOT(rs.getDate("DOT"));
            p.setDept(rs.getString("Dept"));
            p.setAddress(rs.getString("Address"));
            p.setPhoneNo(rs.getInt("PhoneNo"));
            p.setGender(rs.getBoolean("Gender"));
            p.setDOB(rs.getDate("DOB"));
            p.setUsername(rs.getString("Username"));
            p.setPassword(rs.getString("Password"));
            p.setLevel(rs.getInt("Level"));
            p.setStatus(rs.getBoolean("Status"));
            list.add(p);
        }

        return list;

    }

    private java.util.List<Report> getReport(ResultSet rs) throws SQLException {
        java.util.List<Report> list = new ArrayList<Report>();
        while (rs.next()) {
            Report r = new Report();
            r.setReportID(rs.getInt("ReportID"));
            r.setPatientName(rs.getString("PatientName"));
            r.setTName(rs.getString("TName"));
            r.setDOT(rs.getDate("DOT"));
            r.setReport(rs.getString("Report"));
            r.setStaffName(rs.getString("StaffName"));
            r.setD_Name(rs.getString("D_Name"));
            r.setComment(rs.getString("Comment"));
            r.setCost(rs.getInt("Cost"));
            list.add(r);
        }

        return list;

    }

    private java.util.List<Billing> getBilling(ResultSet rs) throws SQLException {
        java.util.List<Billing> list = new ArrayList<Billing>();
        while (rs.next()) {
            Billing r = new Billing();
            r.setBillNo(rs.getInt("BillNo"));
            r.setPName(rs.getString("PatientName"));
            r.setDOT(rs.getDate("DOT"));
            r.setTName(rs.getString("TName"));
            r.setTCost(rs.getInt("Cost"));
            list.add(r);
        }

        return list;

    }

    /*-------------------SelectAll-------------------------------------*/
    public java.util.List<Doctor> getAllDoctor() throws SQLException {
        java.sql.Statement s = cn.createStatement();
        String sql = "Select * from DoctorDetail";
        return getDoctor(s.executeQuery(sql));
    }

    public java.util.List<Staff> getAllStaff() throws SQLException {
        java.sql.Statement s = cn.createStatement();
        String sql = "Select * from StaffDetail";
        return getStaff(s.executeQuery(sql));
    }

    public java.util.List<Staff> getPartStaff() throws SQLException {
        java.sql.Statement s = cn.createStatement();
        String sql = "Select * from V_StaffInfo";
        return getPStaff(s.executeQuery(sql));
    }

    public java.util.List<Test> getAllTest() throws SQLException {
        java.sql.Statement s = cn.createStatement();
        String sql = "Select * from Test";
        return getTest(s.executeQuery(sql));
    }

    public java.util.List<Patient> getAllPatient() throws SQLException {
        java.sql.Statement s = cn.createStatement();
        String sql = "Select * from PatientDetail";
        return getPatient(s.executeQuery(sql));
    }

    public java.util.List<Stock> getAllStock() throws SQLException {
        java.sql.Statement s = cn.createStatement();
        String sql = "Select * from StockDetail";
        return getStock(s.executeQuery(sql));
    }

    public java.util.List<Report> getAllReport() throws SQLException {
        java.sql.Statement s = cn.createStatement();
        String sql = "Select * from V_PatientReport";
        return getReport(s.executeQuery(sql));
    }

    public java.util.List<Billing> getAllBilling() throws SQLException {
        java.sql.Statement s = cn.createStatement();
        String sql = "Select * from V_PatientBilling";
        return getBilling(s.executeQuery(sql));
    }

    /*----------------------------Add----------------------------------*/
    public int AddDoctor(String D_Name, String Address, int PhoneNo, String Specialization) throws SQLException {
        String sql = "insert into DoctorDetail values(?,?,?,?)";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, D_Name);
        pstm.setString(2, Address);
        pstm.setInt(3, PhoneNo);
        pstm.setString(4, Specialization);
        return pstm.executeUpdate();
    }

    public int AddStock(String IName, int ICost, int INum, int DNum) throws SQLException {
        String sql = "insert into StockDetail values(?,?,?,?)";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, IName);
        pstm.setInt(2, ICost);
        pstm.setInt(3, INum);
        pstm.setInt(4, DNum);
        return pstm.executeUpdate();
    }

    public int AddTest(Test t) throws SQLException {
        String sql = "insert into Test values(?,?)";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, t.getTName());
        pstm.setInt(2, t.getCost());
        return pstm.executeUpdate();
    }

    public int AddBilling(int BillNo, int ReportID, int Amount) throws SQLException {
        String sql = "insert into Billing values(?,?,?)";
        pstm = cn.prepareStatement(sql);
        pstm.setInt(1, BillNo);
        pstm.setInt(2, ReportID);
        pstm.setInt(3, Amount);
        return pstm.executeUpdate();
    }

    public int AddStaff(String FName, String LName, Date DOJ, String Dept, String Address, int P_No, boolean Gender, Date DOB, String Edu_qual, String Tech_Skills, String Username, String Password, boolean Status) throws SQLException {
        String sql = "insert into StaffDetail values(?,?,?,?,?,?,?,?,?,?,?,?,0,?)";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, FName);
        pstm.setString(2, LName);
        pstm.setDate(3, (java.sql.Date) DOJ);
        pstm.setString(4, Dept);
        pstm.setString(5, Address);
        pstm.setInt(6, P_No);
        pstm.setBoolean(7, Gender);
        pstm.setDate(8, (java.sql.Date) DOB);
        pstm.setString(9, Edu_qual);
        pstm.setString(10, Tech_Skills);
        pstm.setString(11, Username);
        pstm.setString(12, Password);
        pstm.setBoolean(13, Status);
        return pstm.executeUpdate();
    }

    public int AddPatient(String FName, String LName, Date DOT, String Dept, String Address, int PhoneNo, boolean Gender, Date DOB, String Test, int Bill, String Username, String Password, int Level, boolean Status) throws SQLException {
        String sql = "insert into PatientDetail values(?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, FName);
        pstm.setString(2, LName);
        pstm.setDate(3, (java.sql.Date) DOT);
        pstm.setString(4, Dept);
        pstm.setString(5, Address);
        pstm.setInt(6, PhoneNo);
        pstm.setBoolean(7, Gender);
        pstm.setDate(8, (java.sql.Date) DOB);
        pstm.setString(9, Test);
        pstm.setInt(10, Bill);
        pstm.setString(11, Username);
        pstm.setString(12, Password);
        pstm.setInt(13, Level);
        pstm.setBoolean(14, Status);
        return pstm.executeUpdate();
    }

    public int AddReport(int patientID, int staffID, int doctorID, int testID, String report, String comment) throws SQLException {
        String sql = "insert into Report values (?,?,?,?,?,?)";
        pstm = cn.prepareStatement(sql);
        pstm.setInt(1, patientID);
        pstm.setInt(2, staffID);
        pstm.setInt(3, doctorID);
        pstm.setString(4, report);
        pstm.setInt(5, testID);
        pstm.setString(6, comment);
        return pstm.executeUpdate();
    }

    /*----------------------------Edit-----------------------------*/
    public int EditDoctor(int DoctorID, String D_Name, String Address, int PhoneNo, String Specialization) throws SQLException {
        String sql = "Update DoctorDetail SET D_Name=?,Address=?,PhoneNo=?,Specialization=? WHERE  DoctorID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, D_Name);
        pstm.setString(2, Address);
        pstm.setInt(3, PhoneNo);
        pstm.setString(4, Specialization);
        pstm.setInt(5, DoctorID);
        return pstm.executeUpdate();
    }

    public int EditStock(int ItemCode, String IName, int ICost, int INum, int DNum) throws SQLException {
        String sql = "Update StockDetail SET INum=?,DNum=?,IName=?,ICost=? WHERE ItemCode=?";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, IName);
        pstm.setInt(2, ICost);
        pstm.setInt(3, INum);
        pstm.setInt(4, DNum);
        pstm.setInt(5, ItemCode);
        return pstm.executeUpdate();
    }

    public int EditStaff(int StaffID, String FName, String LName, Date DOJ, String Dept, String Address, int P_No, boolean Gender, Date DOB, String Edu_qual, String Tech_Skills, boolean Status) throws SQLException {
        String sql = "Update StaffDetail SET FName=?,LName=?,DOJ=?,Dept=?,Address=?,P_No=?,Gender=?,DOB=?,Edu_qual=?,Tech_Skills=?,Level=1,Status=? WHERE StaffID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, FName);
        pstm.setString(2, LName);
        pstm.setDate(3, (java.sql.Date) DOJ);
        pstm.setString(4, Dept);
        pstm.setString(5, Address);
        pstm.setInt(6, P_No);
        pstm.setBoolean(7, Gender);
        pstm.setDate(8, (java.sql.Date) DOB);
        pstm.setString(9, Edu_qual);
        pstm.setString(10, Tech_Skills);
        pstm.setBoolean(11, Status);
        pstm.setInt(12, StaffID);
        return pstm.executeUpdate();
    }

    public int EditPatient(int PatientID, String FName, String LName, Date DOT, String Dept, String Address, int PhoneNo, boolean Gender, Date DOB, String Test, int Bill, String Username, String Password, int Level, boolean Status) throws SQLException {
        String sql = "Update PatientDetail SET FName=?,LName=?,DOJ=?,Dept=?,Address=?,PhoneNo=?,Gender=?,DOB=?,Test=?,Bill=?,Username=?,Password=?,Level=?,Status=? WHERE StaffID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, FName);
        pstm.setString(2, LName);
        pstm.setDate(3, (java.sql.Date) DOT);
        pstm.setString(4, Dept);
        pstm.setString(5, Address);
        pstm.setInt(6, PhoneNo);
        pstm.setBoolean(7, Gender);
        pstm.setDate(8, (java.sql.Date) DOB);
        pstm.setString(9, Test);
        pstm.setInt(10, Bill);
        pstm.setString(11, Username);
        pstm.setString(12, Password);
        pstm.setInt(13, Level);
        pstm.setBoolean(14, Status);
        pstm.setInt(15, PatientID);
        return pstm.executeUpdate();
    }

    public int EditTest(int TestID, String TName, int Cost) throws SQLException {
        String sql = "Update Test SET Tname=?,Cost=? WHERE TestID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, TName);
        pstm.setInt(2, Cost);
        pstm.setInt(3, TestID);
        return pstm.executeUpdate();
    }

    public int EditReport(int ReportID, String report) throws SQLException {
        String sql = "Update Report SET Report=? WHERE ReportID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, report);
        pstm.setInt(2, ReportID);
        return pstm.executeUpdate();
    }

    public int EditComment(int ReportID, String comment) throws SQLException {
        String sql = "Update Report SET Comment=? WHERE ReportID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setString(1, comment);
        pstm.setInt(2, ReportID);
        return pstm.executeUpdate();
    }

    /*----------------------Delete---------------------------*/
    public int DeleteDoctor(int DoctorID) throws SQLException {
        String sql = "delete from DoctorDetail where DoctorID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setInt(1, DoctorID);
        return pstm.executeUpdate();

    }

    public int DeleteTest(int TestID) throws SQLException {
        String sql = "delete from Test where TestID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setInt(1, TestID);
        return pstm.executeUpdate();

    }

    public int DeleteStock(int ItemCode) throws SQLException {
        String sql = "delete from StockDetail where ItemCode=?";
        pstm = cn.prepareStatement(sql);
        pstm.setInt(1, ItemCode);
        return pstm.executeUpdate();

    }

    public int DeleteStaff(int StaffID) throws SQLException {
        String sql = "delete from StaffDetail where StaffID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setInt(1, StaffID);
        return pstm.executeUpdate();

    }

    public int DeletePatient(int PatientID) throws SQLException {
        String sql = "delete from PatientDetail where PatientID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setInt(1, PatientID);
        return pstm.executeUpdate();

    }

    /*-----------------------------Search-----------------------*/
    public java.util.List<Doctor> SearchDoctor(int DoctorID) throws SQLException {
        pstm = cn.prepareStatement("select * from DoctorDetail where Gender=?");
        pstm.setInt(1, DoctorID);
        return getDoctor(pstm.executeQuery());
    }

    public java.util.List<Test> SearchTest(int TestID) throws SQLException {
        pstm = cn.prepareStatement("select * from Test where TestID=?");
        pstm.setInt(1, TestID);
        return getTest(pstm.executeQuery());
    }

    public java.util.List<Stock> SearchStock(int StockID) throws SQLException {
        pstm = cn.prepareStatement("select * from StockDetail where StockID=?");
        pstm.setInt(1, StockID);
        return getStock(pstm.executeQuery());
    }

    public java.util.List<Patient> SearchPatient(int PatientID) throws SQLException {
        pstm = cn.prepareStatement("select * from PatientDetail where PatientID=?");
        pstm.setInt(1, PatientID);
        return getPatient(pstm.executeQuery());
    }

    public java.util.List<Staff> SearchStaff(int StaffID) throws SQLException {
        pstm = cn.prepareStatement("select * from StaffDetail where StaffID=?");
        pstm.setInt(1, StaffID);
        return getStaff(pstm.executeQuery());
    }

    public int UpdatePatientStatus(int ID, boolean status) throws SQLException {
        String sql = "Update PatientDetail SET Status=? WHERE PatientID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setBoolean(1, status);
        pstm.setInt(2, ID);
        return pstm.executeUpdate();
    }

    public int UpdateStaffStatus(int ID, boolean status) throws SQLException {
        String sql = "Update StaffDetail SET Status=? WHERE StaffID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setBoolean(1, status);
        pstm.setInt(2, ID);
        return pstm.executeUpdate();
    }

    public int UpdateStaffForPatient(int StaffID, int PatientID) throws SQLException {
        String sql = "Update PatientDetail SET StaffID=? WHERE PatientID=?";
        pstm = cn.prepareStatement(sql);
        pstm.setInt(1, StaffID);
        pstm.setInt(2, PatientID);
        return pstm.executeUpdate();
    }
}
